package smartcrud.spring.models.crud;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import smartcrud.common.orm.Page;
import smartcrud.common.orm.PropertyFilter;
import smartcrud.common.orm.jdbc.Sql;

@Repository
public class HelloDaoJdbcImpl implements HelloDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public Hello createHello(final Hello hello) {
		final String sql = "insert into crud_hello(name,age,money,at_work,icon,introduce,create_date)"
				+ " values (?,?,?,?,?,?,now())";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(/* sql, */new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql,
						Statement.RETURN_GENERATED_KEYS);
				int z = 0;
				ps.setString(++z, hello.getName());
				ps.setInt(++z, hello.getAge());
				ps.setDouble(++z, hello.getMoney());
				ps.setBoolean(++z, hello.getAtWork());
				ps.setBytes(++z, hello.getIcon());
				ps.setString(++z, hello.getIntroduce());
				return ps;
			}
		}, keyHolder);
		hello.setId(keyHolder.getKey().longValue());
		return hello;
	}

	@Override
	public void deleteHello(Long id) {
		jdbcTemplate.update("delete from crud_hello where id=?", id);
	}

	@Override
	public void updateHello(Hello hello) {
		final String sql = "update crud_hello set name=?,age=?,money=?,at_work=?,icon=?,introduce=? where id=?";
		jdbcTemplate.update(sql, hello.getName(), hello.getAge(),
				hello.getMoney(), hello.getAtWork(), hello.getIcon(),
				hello.getIntroduce(), hello.getId());
	}

	@Override
	public Hello findHelloById(Long id) {
		final String sql = "select * from crud_hello where id=?";
		// return jdbcTemplate.queryForObject(sql, Hello.class, id);
		return jdbcTemplate.queryForObject(sql, new RowMapper<Hello>() {
			@Override
			public Hello mapRow(ResultSet rs, int rowNum) throws SQLException {
				Hello hello = new Hello();
				hello.setId(rs.getLong("id"));
				hello.setName(rs.getString("name"));
				hello.setAge(rs.getInt("age"));
				hello.setMoney(rs.getDouble("money"));
				hello.setAtWork(rs.getBoolean("at_work"));
				hello.setIcon(rs.getBytes("icon"));
				hello.setIntroduce(rs.getString("introduce"));
				hello.setCreateDate(rs.getTimestamp("create_date"));
				return hello;
			}
		}, id);
	}

	@Override
	public List<Hello> findAllHellos() {
		final String sql = "select * from crud_hello";
		return jdbcTemplate.query(sql, new RowMapper<Hello>() {
			@Override
			public Hello mapRow(ResultSet rs, int rowNum) throws SQLException {
				Hello hello = new Hello();
				hello.setId(rs.getLong("id"));
				hello.setName(rs.getString("name"));
				hello.setAge(rs.getInt("age"));
				hello.setMoney(rs.getDouble("money"));
				hello.setAtWork(rs.getBoolean("at_work"));
				hello.setIcon(rs.getBytes("icon"));
				hello.setIntroduce(rs.getString("introduce"));
				hello.setCreateDate(rs.getDate("create_date"));
				return hello;
			}
		});
	}

	@Override
	public int count(Page<Hello> page,final List<PropertyFilter> filters) {
		Sql sql = new Sql("select count(*) from crud_hello where 1=1 ", page,filters);
		sql.build4count();
		return jdbcTemplate.queryForInt(sql.getSqlBody(), sql.getSqlParams());
	}

	@Override
	public Page<Hello> findByPage(Page<Hello> page, final List<PropertyFilter> filters) {
		if (page.isAutoCount()) {
			int totalCount = count(page, filters);
			page.setTotalCount(totalCount);
		}
		
		
		Sql sql = new Sql("select * from crud_hello where 1=1 ", page, filters);
		sql.build();

		List<Hello> content = jdbcTemplate.query(sql.getSqlBody(),
				new RowMapper<Hello>() {
					@Override
					public Hello mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Hello hello = new Hello();
						hello.setId(rs.getLong("id"));
						hello.setName(rs.getString("name"));
						hello.setAge(rs.getInt("age"));
						hello.setMoney(rs.getDouble("money"));
						hello.setAtWork(rs.getBoolean("at_work"));
						hello.setIcon(rs.getBytes("icon"));
						hello.setIntroduce(rs.getString("introduce"));
						hello.setCreateDate(rs.getDate("create_date"));
						return hello;
					}
				}, sql.getSqlParams());

		page.setContent(content);
		return page;
	}

}
